<?php

 class Producto_model extends CI_Model {
   
    function __construct(){   
        parent::__construct();
        $this->config->load('database_tables');
        $this->_table = $this->config->item('dbtables');
    }
	
    function getProductos($selects = null, $wheres = null, $empresa = null){
        $this->db->select($this->_table['producto'].'.*, '.$this->_table['producto_tipo'].'.*');
        $this->db->select($this->_table['producto'].'.id as "id", '.$this->_table['producto'].'.descripcion as "descripcion"');
        $this->db->select($this->_table['producto_tipo'].'.id as "id_tipo", '.$this->_table['producto_tipo'].'.descripcion as "desc_tipo"');
        $this->db->join($this->_table['producto_tipo'], $this->_table['producto'].'.fk_id_producto_tipo = '.$this->_table['producto_tipo'].'.id');
        if($wheres != null){
            $this->db->where_in($this->_table['producto'].'.id', explode(",", $wheres));
        }
        if($empresa != null){
            $this->db->where('fk_id_empresa', $empresa);
        }
        return $this->db->get($this->_table['producto']);
    }
    
    function buscar($resultados = 10, $pagina = 0, $parametros = null){
        $this->load->library('General');
        $parametros = explode("_", $parametros);

        $entorno = $parametros[0];
        $fechades = $parametros[3];
        $fechahas = $parametros[4];

        $diferencia_dias = $fechahas - $fechades;
        $personas = $parametros[1];
        $select  = " SELECT ".$this->_table['producto'].".id as 'product_id', ".$this->_table['producto'].".*, ".$this->_table['municipios'].".*";
        $select .= " FROM ".$this->_table['producto'];
        $select .= " JOIN ".$this->_table['municipios']." ON ".$this->_table['municipios'].".id = ".$this->_table['producto'].".fk_id_municipio";
        $select .= " WHERE 1 = 1";
        if($entorno > 0){
            $select .= " AND ".$this->_table['producto'].".fk_id_entorno = ".$entorno;
        }
        $select .= " AND (numero_inquilinos >= ".($personas != '' ? $personas : 0)." OR numero_inquilinos IS NULL)";
        if($parametros[2] != ''){
            $select .= " AND ".$this->_table['producto'].".fk_id_provincia IN (".$parametros[2].")";
        }
        if($fechades != '' && $fechahas != ''){
            $select .= " AND NOT EXISTS (SELECT id FROM ".$this->_table['reserva'];
            $select .= " WHERE date_format(fecha_desde, '%Y%m%d') BETWEEN ".$fechades." AND ".$fechahas;
            $select .= " OR date_format(fecha_hasta, '%Y%m%d') BETWEEN ".$fechades." AND ".$fechahas;
            $select .= " )";
            $select .= " AND EXISTS (select 1 from ".$this->_table['tarifa'];
            $select .= " WHERE ".$this->_table['tarifa'].".fk_id_producto = ".$this->_table['producto'].".id";
            $select .= " AND fecha between date_format(".$fechades.", '%Y%m%d') AND date_format(".$fechahas.", '%Y%m%d')";
            $select .= " AND DATEDIFF(date_format(".$fechahas.", '%Y%m%d'), date_format(".$fechades.", '%Y%m%d')) = ".$diferencia_dias.")";
        }
        $select .= " ORDER BY verificado DESC, comision DESC, valoracion DESC";
        $select .= " LIMIT ".($pagina * $resultados).", ".(($pagina + 1) * $resultados);
        $query = $this->db->query($select);
        log_message('error', $this->db->last_query());
        return $query;
    }
    
    function buscar_portada($registros = 0){
        $select  = " SELECT *";
        $select .= " FROM ".$this->_table['producto'];
        $select .= " JOIN ".$this->_table['municipios']." ON ".$this->_table['municipios'].".id = fk_id_municipio";
        $select .= " WHERE 1 = 1";
        $select .= " AND EXISTS (select 1 from ".$this->_table['tarifa'];
        $select .= " WHERE fk_id_producto = ".$this->_table['producto'].".id)";
        $select .= " ORDER BY verificado DESC, comision DESC";//, valoracion DESC";
        $select .= " LIMIT 0,".$registros;
        $query = $this->db->query($select);
        return $query;
    }
    
    function getFoto($producto = '', $defecto = ''){
        $this->db->select('id AS "id", url AS "desc", url AS "url", fk_id_empresa as "empresa", fk_id_producto as "producto"');
        $this->db->where('fk_id_producto', $producto);
        $this->db->order_by('orden');
        if($defecto != ''){
            $this->db->limit(1);
        }
        return $this->db->get($this->_table['fotos']);
    }
    
    function getContenido(){
        $this->db->select('id AS "id", nombre AS "desc", fk_id_contenido_tipo AS "tipo"');
        $this->db->from($this->_table['contenido']);
        $this->db->order_by('fk_id_contenido_tipo');
        return $this->db->get();        
    }
    
    function getValorContenido($producto, $contenido){
        $this->db->select('id AS "id", valor AS "desc"');
        $this->db->from($this->_table['contenido_producto__nn']);
        $this->db->where('fk_id_producto', $producto);
        $this->db->where('fk_id_contenido', $contenido);
        return $this->db->get();        
    }
    
    function get_comentarios($producto = '', $origen = ''){
        $this->db->where('origen', $origen);
        $this->db->where('fk_id_producto', $producto);
        return $this->db->get($this->_table['comentario']);
    }
    
    function getUltimasProvinciasConReserva(){
        $this->db->select('nombre');
        $this->db->from($this->_table['provincias']);
        $this->db->join($this->_table['producto'], $this->_table['provincias'].'.id = fk_id_provincia');
        $this->db->limit(15, 0);
        return $this->db->get();        
    }
    
    function get_info_usuario($producto = '', $empresa = ''){
        if($empresa != ''){
            $this->db->where('fk_id_empresa', $this->ion_auth->user()->row()->company);
        }
        $this->db->where('id', $producto);
        $query = $this->db->get($this->_table['producto']);
        if($query->num_rows() > 0){
            $row = $query->row_array();
            $this->db->where('id', $row['fk_id_users']);
            return $this->db->get($this->_table['users']);
        }
        return null;
    }
    
    function get_productos_usuario($usuario = null, $producto = null){
        $this->db->from($this->_table['producto']);
        $this->db->join($this->_table['municipios'], $this->_table['municipios'].'.id = fk_id_municipio');
        $this->db->where('fk_id_users', $usuario);
        $this->db->where($this->_table['producto'].'.id !=', $producto);
        $this->db->order_by('RAND()');
        $this->db->limit(4, 0);
        return $this->db->get();
    }
    
    function get_suma_tarifas($producto = null, $fechades = null, $fechahas = null){
        $this->db->select_sum('importe');
        $this->db->from($this->_table['tarifa']);
        $this->db->where('fk_id_producto', $producto);
        if($fechades != null && $fechahas != null){
            $this->db->where("fecha between date_format(".$fechades.", '%Y%m%d')+1 AND date_format(".$fechahas.", '%Y%m%d')");
        }
        $query = $this->db->get();
        if($query->num_rows() > 0){
            $row = $query->row_array();
            return $row['importe'];
        }
        return 0;
    }
    
    function getProvincia($provincia = null){
        $this->db->select('id AS "id", nombre AS "desc"');
        $this->db->from($this->_table['provincias']);
        if($provincia != null){
            $this->db->where('id', $provincia);
        }
        return $this->db->get();        
    }
    
    function getMunicipio($provincia = '', $municipio = ''){
        if($provincia == '' && $municipio != ''){
            $this->db->select('fk_id_provincia');
            $this->db->from($this->_table['municipios']);
            $this->db->where('id', $municipio);
            $query = $this->db->get();
            if($query->num_rows() > 0){
                $row = $query->row_array();
                $provincia = $row['fk_id_provincia'];
            }
        }
        $this->db->select('id AS "id", nombre AS "desc"');
        $this->db->from($this->_table['municipios']);
        if($provincia != '' && $municipio != ''){
            $this->db->where('id', $municipio);
        }
        $this->db->where('fk_id_provincia', $provincia);
        return $this->db->get();        
    }
    
    function getEntorno($entorno = null){
        $this->db->select('id AS "id", descripcion AS "desc"');
        if($entorno != null){
            $this->db->where('id', $entorno);
        }
        $this->db->from($this->_table['entorno']);
        return $this->db->get();        
    }
    
    function getDistancia($producto = ''){
        $this->db->select('id AS "id", nombre AS "desc"');
        $this->db->from($this->_table['distancia']);
        return $this->db->get();        
    }
    
    function getValorDistancia($producto, $distancia){
        $this->db->select('id AS "id", valor AS "desc"');
        $this->db->from($this->_table['distancia_producto__nn']);
        $this->db->where('fk_id_producto', $producto);
        $this->db->where('fk_id_distancia', $distancia);
        return $this->db->get();        
    }
    
    function getDistribucion(){
        $this->db->select('id AS "id", descripcion AS "desc"');
        $this->db->from($this->_table['distribucion']);
        return $this->db->get();        
    }
    
    function getValorDistribucion($producto, $distribucion){
        $this->db->select('id AS "id", valor AS "desc"');
        $this->db->from($this->_table['distribucion_producto__nn']);
        $this->db->where('fk_id_producto', $producto);
        $this->db->where('fk_id_distribucion', $distribucion);
        return $this->db->get();        
    }
}